from contextlib import asynccontextmanager
from datetime import datetime
from typing import Optional, Dict, List, Any, AsyncIterator

import aiomysql
from aiomysql import DictCursor
from pymysql.constants import FIELD_TYPE
from pymysql.converters import conversions


class AsyncDatabaseHelper:
    def __init__(self, host: str, port: int, user: str, password: str, db_name: str, pool_size: int = 10):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db_name = db_name
        self.pool_size = pool_size
        self.pool = None

    async def create_pool(self):
        self.pool = await aiomysql.create_pool(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.db_name,
            minsize=1,
            maxsize=self.pool_size,
            cursorclass=DictCursor
        )

    @asynccontextmanager
    async def get_connection(self) -> AsyncIterator[aiomysql.Connection]:
        if self.pool is None:
            await self.create_pool()
        conn = await self.pool.acquire()
        try:
            yield conn
        finally:
            self.pool.release(conn)

    @asynccontextmanager
    async def get_cursor(self) -> AsyncIterator[aiomysql.Cursor]:
        async with self.get_connection() as conn:
            cursor = await conn.cursor(aiomysql.DictCursor)
            try:
                yield cursor
                await conn.commit()
            except Exception as e:
                await conn.rollback()
                raise e
            finally:
                await cursor.close()

    async def execute(self, query: str, params: Optional[tuple] = None) -> int:
        async with self.get_cursor() as cursor:
            await cursor.execute(query, params or ())
            return cursor.lastrowid

    async def fetch_one(self, query: str, params: Optional[tuple] = None) -> Optional[Dict[str, Any]]:
        async with self.get_cursor() as cursor:
            await cursor.execute(query, params or ())
            return await cursor.fetchone()

    async def fetch_all(self, query: str, params: Optional[tuple] = None) -> List[Dict[str, Any]]:
        async with self.get_cursor() as cursor:
            await cursor.execute(query, params or ())
            return await cursor.fetchall()

    async def close_pool(self):
        if self.pool is not None:
            self.pool.close()
            await self.pool.wait_closed()
